﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using thinger.VisitionDAL;

namespace thinger.VisionDemo
{
    /*************说明文档************
    //文件检测与处理类：NiceFileProduce
    //读写电子表格类：NiceExcelSaveAndRead
    
    *********************************/


    /*************文件处理类*************/
    public class NiceFileProduce
    {
        //分解路径用枚举
        public enum DecomposePathEnum
        {
            PathOnly = 0,//仅返回路径
            NameAndExtension = 1,//返回文件名+扩展名
            NameOnly = 2,//仅返回文件名
            ExtensionOnly = 3,//仅返回扩展名(带.)

        }

        //------------【函数：将文件路径分解】------------  

        //filePath文件路径
        //DecomposePathEnum返回类型
        //------------------------------------------------
        public static string DecomposePathAndName(string filePath, DecomposePathEnum decomposePathEnum)
        {
            string result = "";
            switch (decomposePathEnum)
            {
                case DecomposePathEnum.PathOnly://仅返回路径
                    result = filePath.Substring(0, filePath.LastIndexOf("\\"));
                    break;
                case DecomposePathEnum.NameAndExtension://返回文件名+扩展名
                    result = filePath.Substring(filePath.LastIndexOf("\\") + 1);
                    break;
                case DecomposePathEnum.NameOnly://仅返回文件名
                    result = filePath.Substring(filePath.LastIndexOf("\\") + 1, filePath.LastIndexOf(".") - filePath.LastIndexOf("\\") - 1);
                    break;
                case DecomposePathEnum.ExtensionOnly://仅返回扩展名(带.)
                    result = filePath.Substring(filePath.LastIndexOf("."));
                    break;
                default://
                    result = "";
                    break;
            }
            return result;
        }


        //------------【函数：判断文件路径是否存在，不存在则创建】------------  

        //filePath文件夹路径
        //DecomposePathEnum返回类型
        //---------------------------------------------------------------------
        public static string CheckAndCreatPath(string path)
        {
            if (Directory.Exists(path))
            {
                return path;
            }
            else
            {
                if (path.LastIndexOf("\\") <= 0)
                {
                    try
                    {
                        Directory.CreateDirectory(path);
                        return path;
                    }
                    catch
                    {
                        return "error";
                    }
                }
                else
                {
                    if (CheckAndCreatPath(DecomposePathAndName(path, DecomposePathEnum.PathOnly)) == "error")
                    {
                        return "error";
                    }
                    else
                    {
                        Directory.CreateDirectory(path);
                        return path;
                    }
                }
            }
        }
    }


    /*************使用NPOI读写电子表格类*************/
    public class NiceExcelSaveAndRead
    {


        //------------【函数：将表格控件保存至Excel文件(新建/替换)】------------    

        //filePath要保存的目标Excel文件路径名
        //datagGridView要保存至Excel的表格控件
        //------------------------------------------------------------------------
        public static bool SaveToExcelNew(string filePath, DataGridView dataGridView)
        {
            bool result = true;

            FileStream fs = null;//创建一个新的文件流
            HSSFWorkbook workbook = null;//创建一个新的Excel文件
            ISheet sheet = null;//为Excel创建一张工作表

            //定义行数、列数、与当前Excel已有行数
            int rowCount = dataGridView.RowCount;//记录表格中的行数
            int colCount = dataGridView.ColumnCount;//记录表格中的列数

            //判断文件夹是否存在
            if (NiceFileProduce.CheckAndCreatPath(NiceFileProduce.DecomposePathAndName(filePath, NiceFileProduce.DecomposePathEnum.PathOnly)) == "error")
            {
                result = false;
                return result;
            }

            //创建工作表
            try
            {
                fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet1");
                IRow row = sheet.CreateRow(0);
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[0].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Columns[j].HeaderText.ToString());//更改单元格值                  
                    }
                }
            }
            catch
            {
                result = false;
                return result;
            }

            for (int i = 0; i < rowCount; i++)      //行循环
            {
                //防止行数超过Excel限制
                if (i >= 65536)
                {
                    result = false;
                    break;
                }
                IRow row = sheet.CreateRow(1 + i);  //创建行
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[i].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Rows[i].Cells[j].Value.ToString());//更改单元格值                  
                    }
                }
            }
            try
            {
                workbook.Write(fs);
            }
            catch
            {
                result = false;
                return result;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                    fs = null;
                }
                workbook = null;
            }
            return result;
        }

        public static bool SaveToExcelNew_dlist(string filePath, DataGridView dataGridView)
        {

            void addCell(HSSFWorkbook workbook1, ISheet sheet1, int rowCount1, string text1, double text2)
            {
                IRow row1 = sheet1.CreateRow(rowCount1);  //创建行
                ICell cell1 = row1.CreateCell(0);//创建列
                cell1.SetCellValue(text1);//更改单元格值   
                ICell cell2 = row1.CreateCell(1);//创建列
                cell2.SetCellValue(text2);//更改单元格值  


                ICellStyle cellStyle = workbook1.CreateCellStyle();
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                cell2.CellStyle = cellStyle;

            }

            bool result = true;

            FileStream fs = null;//创建一个新的文件流
            HSSFWorkbook workbook = null;//创建一个新的Excel文件
            ISheet sheet = null;//为Excel创建一张工作表

            //定义行数、列数、与当前Excel已有行数
            int rowCount = dataGridView.RowCount;//记录表格中的行数
            int colCount = dataGridView.ColumnCount;//记录表格中的列数

            //判断文件夹是否存在
            if (NiceFileProduce.CheckAndCreatPath(NiceFileProduce.DecomposePathAndName(filePath, NiceFileProduce.DecomposePathEnum.PathOnly)) == "error")
            {
                result = false;
                return result;
            }

            //创建工作表
            try
            {
                fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet1");
                IRow row = sheet.CreateRow(0);
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[0].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Columns[j].HeaderText.ToString());//更改单元格值                  
                    }
                }
            }
            catch
            {
                result = false;
                return result;
            }

            double okcount = 0;
            double ngcount = 0;
            double totalcount = 0;
            double guocount = 0;
            for (int i = 0; i < rowCount; i++)      //行循环
            {
                //防止行数超过Excel限制
                if (i >= 65536)
                {
                    result = false;
                    break;
                }
                IRow row = sheet.CreateRow(1 + i);  //创建行
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[i].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Rows[i].Cells[j].Value.ToString());//更改单元格值
                        if (j == 3)
                        {
                            okcount = okcount + Convert.ToInt32(dataGridView.Rows[i].Cells[j].Value.ToString());
                        }

                        if (j == 4)
                        {
                            ngcount = ngcount + Convert.ToInt32(dataGridView.Rows[i].Cells[j].Value.ToString());
                        }
                        if (j == 2)
                        {
                            totalcount = totalcount + Convert.ToInt32(dataGridView.Rows[i].Cells[j].Value.ToString());
                        }
                        if (j == 9)
                        {
                            guocount = guocount + Convert.ToInt32(dataGridView.Rows[i].Cells[j].Value.ToString());
                        }
                        if (j == 13)
                        {
                            guocount = guocount + Convert.ToInt32(dataGridView.Rows[i].Cells[j].Value.ToString());
                        }

                    }
                }
            }

            addCell(workbook, sheet, colCount, "良率：", Convert.ToDouble((okcount / totalcount)));
            addCell(workbook, sheet, colCount + 1, "不良率：", Convert.ToDouble(ngcount / totalcount));
            addCell(workbook, sheet, colCount + 2, "过检率：", Convert.ToDouble(guocount / (totalcount * 2)));
            //MessageBox.Show($"良率：{ Convert.ToDouble((okcount / totalcount))} ok:{okcount} 总数：{totalcount}，不良率：{Convert.ToDouble(ngcount / totalcount)} ng:{ngcount}，过检率：{Convert.ToDouble(guocount / (totalcount * 2))} guo:{guocount}");

            try
            {
                workbook.Write(fs);
            }
            catch
            {
                result = false;
                return result;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                    fs = null;
                }
                workbook = null;
            }
            return result;
        }



        //------------【函数：将表格控件保存至Excel文件(添加/新建)】------------    
        //filePath要保存的目标Excel文件路径名
        //datagGridView要保存至Excel的表格控件
        //------------------------------------------------
        public static bool SaveToExcelAdd(string filePath, DataGridView dataGridView)
        {
            bool result = true;

            FileStream fs = null;//创建一个新的文件流
            HSSFWorkbook workbook = null;//创建一个新的Excel文件
            ISheet sheet = null;//为Excel创建一张工作表

            //定义行数、列数、与当前Excel已有行数
            int rowCount = dataGridView.RowCount;//记录表格中的行数
            int colCount = dataGridView.ColumnCount;//记录表格中的列数
            int numCount = 0;//Excell最后一行序号

            //判断文件夹是否存在
            if (NiceFileProduce.CheckAndCreatPath(NiceFileProduce.DecomposePathAndName(filePath, NiceFileProduce.DecomposePathEnum.PathOnly)) == "error")
            {
                result = false;
                return result;
            }
            //判断文件是否存在
            if (!File.Exists(filePath))
            {
                try
                {
                    fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet("Sheet1");
                    IRow row = sheet.CreateRow(0);
                    for (int j = 0; j < colCount; j++)  //列循环
                    {
                        if (dataGridView.Columns[j].Visible && dataGridView.Rows[0].Cells[j].Value != null)
                        {
                            ICell cell = row.CreateCell(j);//创建列
                            cell.SetCellValue(dataGridView.Columns[j].HeaderText.ToString());//更改单元格值                  
                        }
                    }
                    workbook.Write(fs);
                }
                catch
                {
                    result = false;
                    return result;
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Close();
                        fs.Dispose();
                        fs = null;
                    }
                    workbook = null;
                }
            }
            //创建指向文件的工作表
            try
            {
                fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook = new HSSFWorkbook(fs);//.xls
                sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    result = false;
                    return result;
                }
                numCount = sheet.LastRowNum + 1;
            }
            catch
            {
                result = false;
                return result;
            }

            for (int i = 0; i < rowCount; i++)      //行循环
            {
                //防止行数超过Excel限制
                if (numCount + i >= 65536)
                {
                    result = false;
                    break;
                }
                IRow row = sheet.CreateRow(numCount + i);  //创建行
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[i].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Rows[i].Cells[j].Value.ToString());//更改单元格值                  
                    }
                }
            }
            try
            {
                fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fs);
            }
            catch
            {
                result = false;
                return result;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                    fs = null;
                }
                workbook = null;
            }
            return result;
        }




        public static bool SaveToExcelAddNew(DataTable data2)
        {
            bool result = true;
            Dictionary<string, List<cell_info>> data = new Dictionary<string, List<cell_info>>();
            FileStream fs = null;//创建一个新的文件流
            HSSFWorkbook workbook = null;//创建一个新的Excel文件
            ISheet sheet = null;//为Excel创建一张工作表
                                //设置data
            data2.Columns["lotid"].ColumnName = "产品号";
            data2.Columns["cellid"].ColumnName = "CellID";
            data2.Columns["img"].ColumnName = "图片路径";
            data2.Columns["imgcenter"].ColumnName = "中心图片路径";
            data2.Columns["state"].ColumnName = "cell状态";
            data2.Columns["createtime"].ColumnName = "时间";
            data2.Columns["lineid"].ColumnName = "搬运编号";
            data2.Columns["workmode"].ColumnName = "检测模式";
            data2.Columns["facemode"].ColumnName = "检测面";
            data2.Columns["id"].ColumnName = "数据序号";
            //定义行数、列数、与当前Excel已有行数
            int rowCount = data2.Rows.Count;//记录表格中的行数
            int colCount = data2.Columns.Count;//记录表格中的列数
            int numCount = 0;//Excell最后一行序号
            ///
            string sFilePath = "D:\\运行日志\\数据记录\\cell";
            // DataTable data2 = MySqlHelper.Get_CellInfoYestoday();
            if (data2.Rows.Count > 0)
            {
                foreach (DataRow roww in data2.Rows)
                {
                    DateTime time = Convert.ToDateTime(roww["时间"].ToString());
                    Directory.CreateDirectory($"{sFilePath}\\{time.ToString("yyyy.MM")}");
                    //判断文件是否存在
                    string filePath = $"{sFilePath}\\{time.ToString("yyyy.MM")}\\{time.ToString("dd")}.xls";

                    cell_info p = new cell_info();
                    p.id = Convert.ToInt32(roww["数据序号"]);
                    p.lotid = roww["产品号"].ToString();
                    p.cellid = Convert.ToInt32(roww["CellID"]);
                    p.img = roww["图片路径"].ToString();
                    p.imgcenter = roww["中心图片路径"].ToString();
                    p.state = Convert.ToInt32(roww["cell状态"]);
                    p.time = roww["时间"].ToString();
                    p.lineid = Convert.ToInt32(roww["搬运编号"]);
                    p.workmode = roww["检测模式"].ToString();
                    p.facename = roww["检测面"].ToString();
                    p.img = roww["CellID"].ToString();


                    if (data.ContainsKey(filePath))
                    {
                        data[filePath].Add(p);
                    }
                    else
                    {
                        List<cell_info> T = new List<cell_info>();
                        T.Add(p);
                        data.Add(filePath, T);
                    }

                    if (!File.Exists(filePath))
                    {
                        try
                        {
                            fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                            workbook = new HSSFWorkbook();
                            sheet = workbook.CreateSheet("Sheet1");
                            IRow row = sheet.CreateRow(0);

                            for (int j = 0; j < colCount; j++)  //列循环
                            {
                                if (!string.IsNullOrEmpty(data2.Columns[j].ColumnName))
                                {
                                    ICell cell = row.CreateCell(j);//创建列
                                    cell.SetCellValue(data2.Columns[j].ColumnName);//更改单元格值                  
                                }
                            }
                            workbook.Write(fs);
                        }
                        catch
                        {
                            result = false;
                            return result;
                        }
                        finally
                        {
                            if (fs != null)
                            {
                                fs.Close();
                                fs.Dispose();
                                fs = null;
                            }
                            workbook = null;
                        }
                    }

                }
            }


            //创建指向文件的工作表
            if (data != null)
            {
                foreach (var item in data)
                {
                    Console.WriteLine($"---- 写入{item.Key} 成功");
                    try
                    {
                        fs = new FileStream(item.Key, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                        workbook = new HSSFWorkbook(fs);//.xls
                        sheet = workbook.GetSheetAt(0);
                        if (sheet == null)
                        {
                            result = false;
                            return result;
                        }
                        numCount = sheet.LastRowNum + 1;
                    }
                    catch
                    {
                        result = false;
                        return result;
                    }
                    for (int i = 0; i < item.Value.Count; i++)      //行循环
                    {
                        //防止行数超过Excel限制
                        if (numCount + i >= 655369999)
                        {
                            result = false;
                            break;
                        }
                        IRow row = sheet.CreateRow(numCount + i);  //创建行
                        for (int j = 0; j < data2.Columns.Count; j++)  //列循环
                        {
                            if (data2.Columns[j].ColumnName == "数据序号")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].id.ToString());//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "产品号")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].lotid);//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "CellID")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].cellid.ToString());//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "搬运编号")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].lineid.ToString());//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "图片路径")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].img);//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "中心图片路径")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].imgcenter);//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "cell状态")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].state.ToString());//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "检测模式")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].workmode);//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "检测面")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].facename);//更改单元格值
                            }
                            if (data2.Columns[j].ColumnName == "时间")
                            {
                                ICell cell = row.CreateCell(j);//创建列
                                cell.SetCellValue(item.Value[i].time);//更改单元格值
                            }
                        }
                    }
                    try
                    {
                        fs = new FileStream(item.Key, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                        workbook.Write(fs);
                    }
                    catch
                    {
                        result = false;
                        return result;
                    }
                    finally
                    {
                        if (fs != null)
                        {
                            fs.Close();
                            fs.Dispose();
                            fs = null;
                        }
                        workbook = null;
                    }


                }
            }
            return result;
        }





        //------------【函数：从Excel文件读取数据到表格控件】------------    
        //filePath为Excel文件路径名
        //datagGridView要显示数据的表格控件
        //------------------------------------------------
        public static bool ReadFromExcel(string filePath, DataGridView dataGridView)
        {
            bool result = true;

            FileStream fs = null;//创建一个新的文件流
            HSSFWorkbook workbook = null;//创建一个新的Excel文件
            ISheet sheet = null;//为Excel创建一张工作表

            //定义行数、列数
            int rowCount = 0;//记录Excel中的行数
            int colCount = 0;//记录Excel中的列数

            //判断文件是否存在
            if (!File.Exists(filePath))
            {
                result = false;
                return result;
            }
            //创建指向文件的工作表
            try
            {
                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                workbook = new HSSFWorkbook(fs);//.xls
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                    fs = null;
                }
                sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    result = false;
                    return result;
                }
                rowCount = sheet.LastRowNum;
                colCount = sheet.GetRow(0).LastCellNum;
                dataGridView.Rows.Clear();
                dataGridView.Columns.Clear();
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    ICell cell = sheet.GetRow(0).GetCell(j);//获取列
                    dataGridView.Columns.Add(j.ToString() + cell.ToString(), cell.ToString());
                }
                for (int i = 1; i < rowCount; i++)      //行循环
                {
                    IRow row = sheet.GetRow(i);  //获取行
                    int index = dataGridView.Rows.Add();
                    colCount = row.LastCellNum;
                    for (int j = 0; j < colCount; j++)  //列循环
                    {
                        ICell cell = row.GetCell(j);//获取列
                        dataGridView.Rows[index].Cells[j].Value = cell.ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
                return result;
            }
            return result;
        }

    }
}
